package com.groot.medical.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.groot.common.util.MedicalDBUtil;
import com.groot.common.util.SimpleDateUtil;
import com.groot.medical.entity.ChronicPattern;
import com.groot.medical.entity.FamilyArchives;
import com.groot.medical.entity.PersonalFiles;

public class ChronicPatternDAOImpl implements ChronicPatternDAO {
	Connection conn = null;
	PreparedStatement ps = null;
	ResultSet rs = null;
	@Override
	public int getAllChronicPatternInfoCounts() {
		int count = 0;
        //sql拼接
		
		StringBuffer sql = new StringBuffer("SELECT COUNT(1) AS id FROM t_chronic_pattern where 1=1");
	
        
       
        //查询结果
        try {
        	 conn = MedicalDBUtil.getConnection();
             ps = conn.prepareStatement(sql.toString());
             rs = ps.executeQuery();
            if(rs.next()) {
                count = rs.getInt("id");
            }
        } catch (SQLException e) {

    		e.printStackTrace();
    		}
    		finally{
    			MedicalDBUtil.closeAll(conn, ps, rs);
    		}
        return count;
	}

	@Override
	public List<ChronicPattern> findChronicPatternInfoByPage(int startIndex, int pageSize) {
    StringBuffer sql = new StringBuffer("SELECT c.chronic_pattern_code,c.farmingcard_code,c.idcard_code,tc.ill_name,c.start_time,c.end_time,pf.`name` FROM t_chronic_pattern c,t_personnel_file pf ,t_chronictype tc WHERE c.idcard_code = pf.idcard_number AND c.ill_name=tc.ill_code");
		
		
        sql.append(" limit ?,? " );
        try {
			conn = MedicalDBUtil.getConnection();
			ps = conn.prepareStatement(sql.toString());
			ps.setInt(1, startIndex);
			ps.setInt(2, pageSize);
			rs = ps.executeQuery();
			List<ChronicPattern> patterns = new ArrayList<>();
			
			while(rs.next()){
				PersonalFiles person = new PersonalFiles();
				ChronicPattern pattern= new ChronicPattern();
				pattern.setChronicPatternCode(rs.getString(1));
				pattern.setFarmingCardCode(rs.getString(2));
				pattern.setIdCardCode(rs.getString(3));
				pattern.setIllName(rs.getString(4));
				pattern.setStartTime(rs.getDate(5));
				pattern.setEndTime(rs.getDate(6));
				person.setName(rs.getString(7));
				pattern.setPerson(person);
				patterns.add(pattern);
				
			}
			
			return patterns;
			
		} catch (SQLException e) {
			e.printStackTrace();
			
		}finally{
			MedicalDBUtil.closeAll(conn, ps, rs);
		}
		return null;
	}

	@Override
	public int getAlreadyPayPerson(String personName) {
		int counts = 0;
		StringBuffer sql = new StringBuffer("SELECT COUNT(id) AS id FROM t_pay WHERE 1=1 ");
		if(personName!=null && !personName.trim().equals("")){
			sql.append(" And name = ?");
		}
		
		 try {
				conn = MedicalDBUtil.getConnection();
				ps = conn.prepareStatement(sql.toString());
				ps.setString(1, personName);
				rs = ps.executeQuery();
				if(rs.next()){
					 counts = rs.getInt("id");
					
					}
		 }catch (SQLException e) {
					e.printStackTrace();
					}
		 			finally{
					MedicalDBUtil.closeAll(conn, ps, rs);
				}
		 return counts;
		

}
	//这个是其他的
	//select MAX(CAST(menu_id as SIGNED INTEGER)) from t_menu ;
	@Override
	public List<PersonalFiles> findAlreadyPayPerson(String personName) {
		//SELECT p.`name`,f.family_address FROM t_pay p,t_family_rchives f WHERE p.family_code=f.family_code  AND p.`name`='钟海清';
		StringBuffer sql = new StringBuffer("SELECT p.`name`,f.family_address,p.`farmingcard_code`,p.idcard_number FROM t_personnel_file p,t_family_rchives f WHERE p.family_code=f.family_code  AND p.`name` LIKE '%"+personName+"%'");
		
		List<PersonalFiles> persons = new ArrayList<>();
		 try {
				conn = MedicalDBUtil.getConnection();
				ps = conn.prepareStatement(sql.toString());
				
				rs = ps.executeQuery();
				while(rs.next()){
					PersonalFiles person = new PersonalFiles();
					person.setName(rs.getString(1));
					person.setOftenAddress(rs.getString(2));
					person.setFarmingcardCode(rs.getString(3));
					person.setIdcardNumber(rs.getString(4));
					persons.add(person);
					}
				return persons;
		 }catch (SQLException e) {
					e.printStackTrace();
					}
		 			finally{
					MedicalDBUtil.closeAll(conn, ps, rs);
				}
		 return null;
	}

	@Override
	public void AddChronicPatternInfo(ChronicPattern pattern) {
		try {
			conn=MedicalDBUtil.getConnection();//通过工具类直接调用getConnection方法获取jdbc连接。
			String sql="insert into t_chronic_pattern(chronic_pattern_code,farmingcard_code,idcard_code,ill_name,start_time,end_time)values(?,?,?,?,?,?)";
			ps=conn.prepareStatement(sql);
			int start = 1;
			ps.setString(start++,pattern.getChronicPatternCode());
			ps.setString(start++,pattern.getFarmingCardCode());
			ps.setString(start++,pattern.getIdCardCode());
			ps.setString(start++,pattern.getIllName());
			ps.setDate(start++, SimpleDateUtil.fomart(pattern.getStartTime()));
			ps.setDate(start++, SimpleDateUtil.fomart(pattern.getEndTime()));
			ps.executeUpdate();
			
			} catch (SQLException e) {

			e.printStackTrace();
			}finally{
				MedicalDBUtil.closeAll(conn, ps, rs);
			}
		
	}

	@Override
	public boolean checkPatternIsExistByIdCardNum(String idcardNumber) {
		int count = 0;
		StringBuffer sql = new StringBuffer("SELECT COUNT(chronic_pattern_code) id FROM t_chronic_pattern WHERE idcard_code=?");
		//查询结果
        try {
        	 conn = MedicalDBUtil.getConnection();
             ps = conn.prepareStatement(sql.toString());
             ps.setString(1, idcardNumber);
 		   
             rs = ps.executeQuery();
            if(rs.next()) {
                count = rs.getInt("id");
            }
            if(count==1){
            	return true;
            }
            
        } catch (SQLException e) {

    		e.printStackTrace();
    		}
    		finally{
    			MedicalDBUtil.closeAll(conn, ps, rs);
    		}
		return false;
	}

	@Override
	public boolean checkChronicNameIsEquals(String idcardNumber, String illName) {
		int count = 0;
		StringBuffer sql = new StringBuffer("SELECT COUNT(chronic_pattern_code) id FROM t_chronic_pattern WHERE idcard_code=? AND ill_name=?;");
		//查询结果
        try {
        	 conn = MedicalDBUtil.getConnection();
             ps = conn.prepareStatement(sql.toString());
             ps.setString(1, idcardNumber);
             ps.setString(2, illName);
             rs = ps.executeQuery();
            if(rs.next()) {
                count = rs.getInt("id");
            }
            if(count==1){
            	return true;
            }
            
        } catch (SQLException e) {

    		e.printStackTrace();
    		}
    		finally{
    			MedicalDBUtil.closeAll(conn, ps, rs);
    		}
		return false;
	}

	@Override
	public boolean checkDateIsOut(String idcardNumber, String date) {
		int count = 0;
		StringBuffer sql = new StringBuffer("SELECT COUNT(chronic_pattern_code) id FROM t_chronic_pattern t WHERE idcard_code= ? AND  ? between start_time and end_time");
		//查询结果
        try {
        	 conn = MedicalDBUtil.getConnection();
             ps = conn.prepareStatement(sql.toString());
             ps.setString(1, idcardNumber);
             
             ps.setString(2, date);
             rs = ps.executeQuery();
            if(rs.next()) {
                count = rs.getInt("id");
            }
            if(count==1){
            	return true;
            }
            
        } catch (SQLException e) {

    		e.printStackTrace();
    		}
    		finally{
    			MedicalDBUtil.closeAll(conn, ps, rs);
    		}
		return false;
	}

	@Override
	public ChronicPattern findChronicInfoByIdCardNum(String idCardNum) {
		StringBuffer sql = new StringBuffer("SELECT tp.ill_name,tc.ill_name from t_chronic_pattern tp ,t_chronictype tc WHERE tp.ill_name=tc.ill_code AND tp.idcard_code=?");
		
		
		 try {
				conn = MedicalDBUtil.getConnection();
				ps = conn.prepareStatement(sql.toString());
				ps.setString(1, idCardNum);
				rs = ps.executeQuery();
				ChronicPattern pattern = null;
				while(rs.next()){
					pattern = new ChronicPattern();
					
					pattern.setFarmingCardCode(rs.getString(1));
					pattern.setIllName(rs.getString(2));
					
					
					}
				return pattern;
		 }catch (SQLException e) {
					e.printStackTrace();
					}
		 			finally{
					MedicalDBUtil.closeAll(conn, ps, rs);
				}
		 return null;
	}

	@Override
	public int checkIsMakePayInfo(String currentYear,String id) {
		int counts = 0;
		StringBuffer sql = new StringBuffer("SELECT COUNT(ce.farmingcard_code) id FROM t_chronic_pattern cp,t_chronic_expense ce WHERE cp.farmingcard_code=ce.farmingcard_code AND ce.current_year=? AND cp.chronic_pattern_code=?");
		 try {
				conn = MedicalDBUtil.getConnection();
				ps = conn.prepareStatement(sql.toString());
				ps.setString(1, currentYear);
				ps.setString(2, id);
				rs = ps.executeQuery();
				if(rs.next()){
					 counts = rs.getInt("id");
					
					}
		 }catch (SQLException e) {
					e.printStackTrace();
					}
		 			finally{
					MedicalDBUtil.closeAll(conn, ps, rs);
				}
		 return counts;
	}

	@Override
	public ChronicPattern findChronicInfoByChronicCode(String id) {
		
		StringBuffer sql = new StringBuffer("SELECT tp.chronic_pattern_code,tp.farmingcard_code,tp.start_time,tp.end_time,tp.ill_name,tc.ill_name,pf.`name` from t_chronic_pattern tp ,t_chronictype tc,t_personnel_file pf WHERE tp.ill_name=tc.ill_code AND tp.idcard_code=pf.idcard_number AND tp.chronic_pattern_code=?");
		
		
		 try {
				conn = MedicalDBUtil.getConnection();
				ps = conn.prepareStatement(sql.toString());
				ps.setString(1, id);
				rs = ps.executeQuery();
				ChronicPattern pattern = null;
				while(rs.next()){
					
					pattern = new ChronicPattern();
					PersonalFiles person = new PersonalFiles();
					pattern.setChronicPatternCode(rs.getString(1));
					pattern.setFarmingCardCode(rs.getString(2));
					pattern.setStartTime(rs.getDate(3));
					pattern.setEndTime(rs.getDate(4));
					pattern.setIllName(rs.getString(5));
					pattern.setIdCardCode(rs.getString(6));
					person.setName(rs.getString(7));
					pattern.setPerson(person);
					}
				return pattern;
		 }catch (SQLException e) {
					e.printStackTrace();
					}
		 			finally{
					MedicalDBUtil.closeAll(conn, ps, rs);
				}
		 return null;
	}

	@Override
	public void deletePattern(String id) {
		try {
			conn=MedicalDBUtil.getConnection();//通过工具类直接调用getConnection方法获取jdbc连接。
			String sql="delete from t_chronic_pattern where chronic_pattern_code=?";
			ps=conn.prepareStatement(sql);
			ps.setString(1, id);
			ps.executeUpdate();
			
			} catch (SQLException e) {

			e.printStackTrace();
			}finally{
				MedicalDBUtil.closeAll(conn, ps, rs);
			}
		
	}
}
